from pymysql import Connection
from datetime import datetime

# 数据库链接
def connect():
    """
    创建并返回一个MySQL数据库连接对象
    :return: Connection: 数据库连接对象，配置了主机、端口、用户、密码、数据库和字符集等参数
        设置autocommit=True确保每次操作后自动提交事务
    """
    return Connection(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        database='month01_exam',
        charset='utf8',
        autocommit=True
    )


def create_table():
    """
    创建日志类型表和日志记录表，初始化日志类型
    """
    db = connect()#
    cursor = db.cursor()
    #创建日志类型表
    cursor.execute("""
        create table if not exists log_types(
            type_name varchar(20) primary key comment '类型名称(主键)',
            type_description varchar(100) not null comment '类型说明',
            type_level varchar(10) not null comment '类型级别'
        )
    """)

    #创建日志记录表
    cursor.execute("""
        create table if not exists log_records (
            id int primary key auto_increment comment '主键ID',
            occurrence_time datetime not null comment '发生时间',
            log_type varchar(20) not null comment '日志类型(外键)',
            log_details text not null comment '日志详情',
             FOREIGN KEY(log_type) REFERENCES log_types
                        (type_name) ON DELETE CASCADE
                                    ON UPDATE CASCADE)
        """)

    # 初始化日志类型
    cursor.execute("select count(*) from log_records")
    if cursor.fetchone()[0] == 0:

        log_types =[
            ('ERROR', '系统错误', '高'),
            ('WARN', '警告信息', '中'),
            ('INFO', '普通信息', '低'),
            ('DEBUG', '调试日志', '低'),
            ('CRITICAL', '严重故障', '极高')
        ]
        for log_type in log_types:
            cursor.execute("INSERT INTO log_types (type_name, type_description, type_level) VALUES (%s, %s, %s)",
                           log_type)
    db.close()

def import_log_data():
    """
    从server_logs.txt文件导入日志数据到数据库
    """
    try:
        # 打开文件并读取所有行
        with open('server_logs.txt', 'r', encoding='utf-8') as file:
            lines = file.readlines()
        db=connect()
        cursor = db.cursor()

        success_count = 0
        error_count = 0
        # 遍历文件中的每一行
        for line in lines:
            line = line.strip()
            if not line:
                continue

            # 分割成3部分：时间,类型,详情
            parts = line.split(',', 2)
            if len(parts) < 3:
                error_count += 1
                continue
            # 提取各个字段并去除空白
            occurrence_time = parts[0].strip()
            log_type = parts[1].strip().upper()
            log_details = parts[2].strip()

            # 插入数据库
            cursor.execute(
                "insert into log_records (occurrence_time, log_type, log_details) values (%s, %s, %s)",
                (occurrence_time, log_type, log_details)
            )
            success_count += 1

        db.close()
        print(f"导入完成！成功: {success_count} 条，失败: {error_count} 条")
    # 异常处理
    except Exception as e:
        print(f"导入过程中发生错误: {e}")


def show_results(rows, title):
    """
    格式化显示查询结果
    """
    print(f"\n===== {title} =====")
    if not rows:
        print("没有找到记录!")
        return
    # 表头
    print("ID\t发生时间\t\t\t类型\t级别\t日志详情")
    print("-" * 80)
    # 遍历并打印每一行结果
    for row in rows:
        log_id = row[0]
        occurrence_time = row[1].strftime("%Y-%m-%d %H:%M:%S") if isinstance(row[1], datetime) else str(row[1])
        log_type = row[2]
        type_level = row[3]
        log_details = row[4][:50] + "..." if len(row[4]) > 50 else row[4]
        # 格式化输出一行记录
        print(f"{log_id}\t{occurrence_time}\t{log_type}\t{type_level}\t{log_details}")


def view_all_logs():
    """
    浏览所有日志记录
    """
    db = connect()
    cursor = db.cursor()
    # 连接查询
    cursor.execute("""
                SELECT lr.id, lr.occurrence_time, lr.log_type, lt.type_level, lr.log_details
                FROM log_records lr
                JOIN log_types lt ON lr.log_type = lt.type_name
                ORDER BY lr.occurrence_time DESC
                   """)
    rows = cursor.fetchall()
    db.close()
    show_results(rows, "所有日志记录")


def add_log_interactive():
    """
    添加日志记录
    """
    # 获取日志类型列表
    db = connect()
    cursor = db.cursor()
    cursor.execute("SELECT type_name, type_description FROM log_types ORDER BY type_name")
    log_types = cursor.fetchall()
    db.close()

    print("可用的日志类型:")
    # 显示所有可用的日志类型供用户选择
    for i, (type_name, description) in enumerate(log_types, 1):
        print(f"{i}. {type_name} - {description}")

    try:
        type_choice = int(input("请选择日志类型编号: "))
        if 1 <= type_choice <= len(log_types):
            selected_type = log_types[type_choice - 1][0]
        else:
            print("无效的选择!")
            return
    except ValueError:
        print("请输入有效的数字!")
        return
    # 获取发生时间
    occurrence_time = input("发生时间 (格式: YYYY-MM-DD HH:MM:SS，留空使用当前时间): ").strip()
    if not occurrence_time:
        occurrence_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    # 获取日志详情
    log_details = input("请输入日志详情: ").strip()
    if not log_details:
        print("日志详情不能为空!")
        return

    # 插入日志记录
    db = connect()
    cursor = db.cursor()
    cursor.execute(
        "insert into log_records (occurrence_time, log_type, log_details) values (%s, %s, %s)",
        (occurrence_time, selected_type, log_details)
    )
    db.close()
    print("日志添加成功!")


def delete_log():
    """
    删除指定ID的日志记录
    """
    try:
        log_id = int(input("请输入要删除的日志ID: "))
        db = connect()
        cursor = db.cursor()
        # 执行删除操作
        affected_rows = cursor.execute("delete from log_records where id = %s", (log_id,))
        db.close()

        if affected_rows > 0:
            print(f"成功删除 {affected_rows} 条日志记录。")
        else:
            print("删除失败或日志不存在。")
    except ValueError:
        print("请输入有效的日志ID!")


def query_logs_by_time_range():
    """
    查询某时间段内的所有日志
    """
    # 获取查询时间范围
    start_time = input("请输入开始时间 (格式: YYYY-MM-DD HH:MM:SS): ").strip()
    end_time = input("请输入结束时间 (格式: YYYY-MM-DD HH:MM:SS): ").strip()

    if not start_time or not end_time:
        print("开始时间和结束时间都不能为空!")
        return

    db = connect()
    cursor = db.cursor()
    # 执行时间段查询
    cursor.execute("""
                select lr.id, lr.occurrence_time, lr.log_type, lt.type_level, lr.log_details
                from log_records lr
                join log_types lt
                on lr.log_type = lt.type_name
                where lr.occurrence_time between %s and %s
                order by lr.occurrence_time desc
                """, (start_time, end_time)
    )
    rows = cursor.fetchall()
    db.close()
    # 显示查询结果
    show_results(rows, f"时间段 {start_time} 到 {end_time} 的日志")


def view_high_severity_logs():
    """
    查看严重级别为高或极高的日志
    """
    db = connect()
    cursor = db.cursor()
    # 查询严重级别为高或极高的日志
    cursor.execute("""
                select lr.id, lr.occurrence_time, lr.log_type, lt.type_level, lr.log_details
                from log_records lr
                join log_types lt on lr.log_type = lt.type_name
                where lt.type_level in ('高', '极高')
                order by lr.occurrence_time desc 
    """)
    rows = cursor.fetchall()
    db.close()
    show_results(rows, "严重级别日志（高/极高）")


def find_top_error_messages():
    """
    找出Top 3最频繁出现的错误消息
    """
    db = connect()
    cursor = db.cursor()
    # 统计ERROR类型的日志详情出现次数
    cursor.execute("""
                select log_details, COUNT(*) as count
                from log_records
                where log_type = 'ERROR'
                group by log_details
                order by count desc
                limit 0,3
    """)
    errors = cursor.fetchall()
    db.close()

    print("\n===== Top 3 最频繁错误消息 =====")
    if not errors:
        print("没有找到错误消息!")
        return
    # 显示Top 3错误消息
    for i, (details, count) in enumerate(errors, 1):
        print(f"{i}. {details} (出现{count}次)")


def count_logs_by_level():
    """
    按级别统计日志数量
    """
    db = connect()
    cursor = db.cursor()
    # 按级别分组统计日志数量
    cursor.execute("""
                select lt.type_description, lt.type_level, COUNT(lr.id) as count
                from log_types lt
                left join log_records lr
                on lt.type_name = lr.log_type
                group by lt.type_description, lt.type_level
                order by
                case lt.type_level
                when '极高' then 1
                when '高' then 2
                when '中' then 3
                when '低' then 4
                else 5
                end
    """)
    stats = cursor.fetchall()
    db.close()

    print("\n===== 日志级别统计 =====")
    if not stats:
        print("没有统计信息!")
        return
    # 显示统计结果
    for description, level, count in stats:
        print(f"{description}[{level}] {count}条")


# 主菜单
def main():
    """
    显示主菜单并处理用户选择
    """
    while True:
        # 显示菜单选项
        print("\n日志管理系统")
        print("1. 浏览所有日志记录")
        print("2. 添加日志记录")
        print("3. 删除日志记录")
        print("4. 查询时间段日志")
        print("5. 查看严重级别日志")
        print("6. 查看Top 3错误消息")
        print("7. 按级别统计日志数量")
        print("8. 退出系统")
        print("0. 导入日志文件数据")
        # 获取用户选择
        choice = input("请选择操作 (0-8): ").strip()
        # 调用相应功能
        if choice == '1':
            view_all_logs()
        elif choice == '2':
            add_log_interactive()
        elif choice == '3':
            delete_log()
        elif choice == '4':
            query_logs_by_time_range()
        elif choice == '5':
            view_high_severity_logs()
        elif choice == '6':
            find_top_error_messages()
        elif choice == '7':
            count_logs_by_level()
        elif choice == '8':
            print("感谢使用日志管理系统!")
            break
        elif choice == '0':
            import_log_data()
        else:
            print("无效的选择，请重新输入!")


# 启动
if __name__ == '__main__':
    create_table()# 创建表结构
    main()# 启动菜单